<?php
/**
 * (logic) 补广告数据
 */
namespace App\Logic\Operation;

use App\Models\Basics\AmazonCountry;
use App\Models\Basics\ExchangeRate;
use App\Models\Basics\SysICountry;
use App\Models\Operation\Ads;
use App\Models\Operation\AdsBC;
use App\Models\Operation\AdsDSP;
use App\Models\Operation\AdsVC;
use App\Models\Operation\BATotal;
use App\Models\Operation\ProfitSku;
use App\Models\Operation\TCTotal;
use App\Models\Operation\TCTotalDetail;
use App\Models\Product\ProductAmazonSkuCCS;
use App\Models\Product\ProductSku;
use App\Models\SalesAmazon\SalesAmountDay;
use App\Models\SalesAmazon\SalesVolumeDay;
use Illuminate\Support\Facades\DB;
class CheckAdsOperation
{
    public static function handle($date)
    {
        $area = [];
        $rate = [];
        $classify = [];
        $time = strtotime($date);
        $_date = date("Ymd",$time);
        //Amazon销售销量

        ProductAmazonSkuCCS::query()
            ->whereHas('classify',function ($query){
                $query->where('status',1);
            })
            ->orderBy('id')
            ->chunk(200, function ($items) use (&$area,&$rate,&$classify,$date,$time,$_date){
                DB::beginTransaction();
                try{
                    foreach ($items as $item){
                        $where = [];
                        $where['report_date'] = $date;
                        $where['country_id'] = $item->country_id;
                        $where['classify_id'] = $item->classify_id;
                        $data = [];
                        $data['seller_id'] = $item->seller_id;
                        if(!array_key_exists($item->country_id,$area)){
                            $area[$item->country_id] = SysICountry::getAreaIdByCountryId($item->country_id);
                        }
                        $data['area_id'] = $area[$item->country_id];
                        $data['profit_rate'] = self::_getProfitRate($item->country_id,$item->classify_id);
                        $data['sessions_app'] = 0;
                        $data['sessions_browser'] = 0;
                        $data['sessions_total'] = 0;
                        $data['page_views_app'] = 0;
                        $data['page_views_browser'] = 0;
                        $data['page_views_total'] = 0;
                        if(!array_key_exists($item->country_id,$rate)){
                            $currencyCode = AmazonCountry::getCurrencyCodeByCountryId($item->country_id);
                            $rate[$item->country_id] = ExchangeRate::getRateOddsToRMB($currencyCode,strtotime($date));
                        }
                        $rateOdds = $rate[$item->country_id];
                        if(!array_key_exists($item->classify_id,$classify)){
                            $classify[$item->classify_id] = ProductSku::query()
                                ->where('classify_id',$item->classify_id)
                                ->pluck('id')
                                ->toArray();
                        }
                        $pSkuIds = $classify[$item->classify_id];
                        $data['ordered'] = self::_getVolume($_date,$item->country_id,$pSkuIds);
                        $amount = self::_getAmount($_date,$item->country_id,$pSkuIds,$rateOdds);
                        $data['ordered_sales'] = $amount['amount'];
                        $data['promotion_discount'] = $amount['promotion_discount'];
                        $ads = self::_getAds($date,$item->country_id,$item->classify_id);
                        $data['impressions'] = $ads['impressions'];
                        $data['clicks'] = $ads['clicks'];
                        $data['total_units'] = $ads['total_units'];
                        $data['spend'] = $ads['spend'] ? round($ads['spend'] * $rateOdds,3) : 0;
                        $data['total_sales'] = $ads['total_sales'] ? round($ads['total_sales'] * $rateOdds,3) : 0;
                        $data['ordered_r'] =  $data['ordered'] - $data['total_units'];
                        $data['spend_os'] = (floatval($data['ordered_sales']) == 0) ? 0 : round($data['spend'] / $data['ordered_sales'],3);
                        $data['reimburse'] = self::_getReimburse($date,$item->country_id,$item->classify_id);
                        $data['profit'] = self::_getProfit($date,$_date,$rateOdds,$item->country_id,$item->classify_id,$pSkuIds,$data['spend'],$data['reimburse'],$data['promotion_discount']);

                        //运营总表
                        BATotal::query()->updateOrCreate($where,$data);
                    }
                    DB::commit();
                }catch (\Exception $e){
                    DB::rollBack();
                    report($e);
                }
        });
    }

//    public static function handlex($date)
//    {
////        die('no data!');
//        $eof = strtotime($date); //日期转时间戳
//
//        $area = [];
//        $rate = [];
//        $classify = [];
//
//        //Amazon销售销量
//        ProductAmazonSkuCCS::query()
//            ->whereHas('classify',function ($query){
//                $query->where('status',1);
//            })
//            ->orderBy('id')
//            ->chunk(200, function ($items) use (&$area,&$rate,&$classify,$eof){
//                DB::beginTransaction();
//                try{
//                    foreach ($items as $item){
//                        $time = strtotime('2024-01-23');
//                        echo '==========start:'.$item->country_id.'-'.$item->classify_id.'========'.PHP_EOL;
//                        while (true){
//                            if($time == $eof)break;
//                            $date = date("Y-m-d",$time);
//                            $_date = date("Ymd",$time);
//                            echo '==========start:'.$item->country_id.'-'.$item->classify_id.'-'.$date.'========'.PHP_EOL;
//                            $where = [];
//                            $where['report_date'] = $date;
//                            $where['country_id'] = $item->country_id;
//                            $where['classify_id'] = $item->classify_id;
//                            $data = [];
//                            $data['seller_id'] = $item->seller_id;
//                            if(!array_key_exists($item->country_id,$area)){
//                                $area[$item->country_id] = SysICountry::getAreaIdByCountryId($item->country_id);
//                            }
//                            $data['area_id'] = $area[$item->country_id];
//                            $data['profit_rate'] = self::_getProfitRate($item->country_id,$item->classify_id);
//                            $data['sessions_app'] = 0;
//                            $data['sessions_browser'] = 0;
//                            $data['sessions_total'] = 0;
//                            $data['page_views_app'] = 0;
//                            $data['page_views_browser'] = 0;
//                            $data['page_views_total'] = 0;
//                            if(!array_key_exists($item->country_id,$rate)){
//                                $currencyCode = AmazonCountry::getCurrencyCodeByCountryId($item->country_id);
//                                $rate[$item->country_id] = ExchangeRate::getRateOddsToRMB($currencyCode,strtotime($date));
//                            }
//                            $rateOdds = $rate[$item->country_id];
//                            if(!array_key_exists($item->classify_id,$classify)){
//                                $classify[$item->classify_id] = ProductSku::query()
//                                    ->where('classify_id',$item->classify_id)
//                                    ->pluck('id')
//                                    ->toArray();
//                            }
//                            $pSkuIds = $classify[$item->classify_id];
//                            $data['ordered'] = self::_getVolume($_date,$item->country_id,$pSkuIds);
//                            $amount = self::_getAmount($_date,$item->country_id,$pSkuIds,$rateOdds);
//                            $data['ordered_sales'] = $amount['amount'];
//                            $data['promotion_discount'] = $amount['promotion_discount'];
//                            $ads = self::_getAds($date,$item->country_id,$item->classify_id);
//                            $data['impressions'] = $ads['impressions'];
//                            $data['clicks'] = $ads['clicks'];
//                            $data['total_units'] = $ads['total_units'];
//                            $data['spend'] = $ads['spend'] ? round($ads['spend'] * $rateOdds,3) : 0;
//                            $data['total_sales'] = $ads['total_sales'] ? round($ads['total_sales'] * $rateOdds,3) : 0;
//                            $data['ordered_r'] =  $data['ordered'] - $data['total_units'];
//                            $data['spend_os'] = (floatval($data['ordered_sales']) == 0) ? 0 : round($data['spend'] / $data['ordered_sales'],3);
//                            $data['reimburse'] = self::_getReimburse($date,$item->country_id,$item->classify_id);
//                            $data['profit'] = self::_getProfit($date,$_date,$rateOdds,$item->country_id,$item->classify_id,$pSkuIds,$data['spend'],$data['reimburse'],$data['promotion_discount']);
//
//                            //运营总表
//                            BATotal::query()->updateOrCreate($where,$data);
//                            echo '==========end:'.$item->country_id.'-'.$item->classify_id.'-'.$date.'========'.PHP_EOL;
//                            $time += 86400;
//                        }
//                        echo '==========end:'.$item->country_id.'-'.$item->classify_id.'========'.PHP_EOL;
//                    }
//                    DB::commit();
//                }catch (\Exception $e){
//                    DB::rollBack();
//                    report($e);
//                }
//            });
//    }

    private static function _getReimburse($date,$countryId,$classifyId){
        $res = DB::table('reimburse_paid as rp')
            ->leftjoin('reimburse_apply as ra', 'rp.reimburse_no', '=', 'ra.reimburse_no')
            ->leftjoin('product_sku as ps', 'ra.psku_id', '=', 'ps.id')
            ->where('ps.classify_id',$classifyId)
            ->where('ra.country_id',$countryId)
            ->where('rp.paid_status',2)
            ->whereDate('rp.paid_date',$date)
            ->select('rp.currency_code','rp.paid_amount')
            ->get();
        $total = 0;
        foreach ($res as $re){
            $rateOdds = ExchangeRate::getRateOddsToRMB($re->currency_code,strtotime($date));
            $total += $re->paid_amount * $rateOdds;
        }
        return round($total,3);
    }

    private static function _getProfit($date,$_date,$rateOdds,$countryId,$classifyId,$pSkuIds,$spend,$reimburse,$promotionDiscount): float
    {
        $amounts = SalesAmountDay::query()
            ->where('day',$_date)
            ->where('country_id',$countryId)
            ->whereIn('psku_id',$pSkuIds)
            ->select(DB::raw('sum(amount) as amount,psku_id'))
            ->groupBy('psku_id')
            ->get();
        $profit = 0;
        $tcItem = [];
        foreach ($amounts as $amount){
            $item = ProfitSku::query()
                ->where('psku_id',$amount->psku_id)
                ->where('country_id',$countryId)
                ->where('transport',1)
                ->first();
            $temp = [];
            $temp['amount'] = $amount->amount;
            if($item){
                $volume = SalesVolumeDay::query()
                    ->where('day',$_date)
                    ->where('country_id',$countryId)
                    ->where('psku_id',$amount->psku_id)
                    ->sum('volume');
                $tpl = round($volume * $item->tpl_rmb / $item->rate_odds,3);
                $headCost = $volume * $item->head_cost;
                $fbaFee = $volume * $item->fba_fee;
                $cost = ProfitSku::getCost($tpl,$amount->amount,$headCost,$fbaFee,$item->commission,$item->return,$item->vat);
                $temp['volume'] = $volume;
                $temp['tpl_rmb'] = $item->tpl_rmb;
                $temp['head_cost'] = $item->head_cost;
                $temp['fba_fee'] = $item->fba_fee;
                $temp['commission'] = $item->commission;
                $temp['return'] = $item->return;
                $temp['vat'] = $item->vat;
                $temp['cost'] = $cost;
            }else{
                $temp['cost'] = 0;
                $cost = 0;
            }
            $_profit = round(($amount->amount - $cost),3);
            $temp['profit'] = $_profit;
            $profit += $_profit * $rateOdds;
            $tcItem[$amount->psku_id] = $temp;
        }
        $item = [];
        $item['report_date'] = $date;
        $item['country_id'] = $countryId;
        $item['classify_id'] = $classifyId;
        $item['rate_odds'] = $rateOdds;
        $item['spend'] = $spend;
        $item['reimburse'] = $reimburse;
        $item['promotion_discount'] = $promotionDiscount;
        $profitMinus = round($profit - $spend - $reimburse - $promotionDiscount,3);
        $item['profit'] = $profitMinus;

        //运营总表
        self::_store($item,$tcItem);

        return $profitMinus;
    }

    /**
     * 核验运营总表
     * @param $item
     * @param $tcItem
     */
    private static function _store($item,$tcItem){
        //运营总表
        $tcTotal = TCTotal::query()->firstOrNew(['report_date' => $item['report_date'], 'country_id' => $item['country_id'], 'classify_id' => $item['classify_id']]);
        foreach ($item as $k => $value ){
            if(in_array($k,['report_date','country_id','classify_id']))continue;
            $tcTotal->{$k} = $value;
        }
        $tcTotal->save();
        //运营总表明细
        foreach ($tcItem as $pSkuId => $_tcItem){
            TCTotalDetail::query()->updateOrCreate(['tc_id' => $tcTotal->id,'psku_id' => $pSkuId],$_tcItem);
        }
    }

    /**
     * 获取毛利率范围
     * @param $countryId
     * @param $classifyId
     * @return string
     */
    private static function _getProfitRate($countryId,$classifyId){
        $profitRate = ProfitSku::query()
            ->where('country_id',$countryId)
            ->whereHas('sku', function ($query) use ($classifyId){
                $query->where('classify_id',$classifyId);
            })
            ->pluck('profit_rate')
            ->toArray();
        return empty($profitRate) ? '0% ~ 0%' : min($profitRate) * 100 . '% ~ ' . max($profitRate) * 100 . '%';
    }

    /**
     * 日销量
     * @param $_date
     * @param $countryId
     * @param $pSkuIds
     * @return int|mixed
     */
    private static function _getVolume($_date,$countryId,$pSkuIds){
        return SalesVolumeDay::query()
            ->where('day',$_date)
            ->where('country_id',$countryId)
            ->whereIn('psku_id',$pSkuIds)
            ->sum('volume');
    }

    /**
     * 日销售额
     * @param $_date
     * @param $countryId
     * @param $pSkuIds
     * @param $rateOdds
     * @return array
     */
    private static function _getAmount($_date,$countryId,$pSkuIds,$rateOdds){
        $amount = SalesAmountDay::query()
            ->where('day',$_date)
            ->where('country_id',$countryId)
            ->whereIn('psku_id',$pSkuIds)
            ->select(DB::raw('sum(amount) as amount,
                sum(promotion_discount) as promotion_discount'))
            ->first();
        $re = [];
        $re['amount'] = round(($amount->amount ?? 0) * $rateOdds,3);
        $re['promotion_discount'] = round(($amount->promotion_discount ?? 0) * $rateOdds,3);
        return $re;
    }

    /**
     * 广告
     * @param $date
     * @param $countryId
     * @param $classifyId
     * @return array
     */
    private static function _getAds($date,$countryId,$classifyId){
        $ads = Ads::query()
            ->where('report_date',$date)
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->select(DB::raw('sum(impressions) as impressions,
                sum(clicks) as clicks,
                sum(total_units) as total_units,
                sum(spend) as spend,
                sum(total_sales) as total_sales'))
            ->first();
        $adsBc = AdsBC::query()
            ->where('report_date',$date)
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->select(DB::raw('sum(impressions) as impressions,
                sum(clicks) as clicks,
                sum(total_units) as total_units,
                sum(spend) as spend,
                sum(total_sales) as total_sales'))
            ->first();
        $adsDSP = AdsDSP::query()
            ->where('report_date',$date)
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->select(DB::raw('sum(impressions) as impressions,
                sum(clicks) as clicks,
                sum(total_units) as total_units,
                sum(spend) as spend,
                sum(total_sales) as total_sales'))
            ->first();
        $adsVc = AdsVC::query()
            ->where('report_date',$date)
            ->where('country_id',$countryId)
            ->where('classify_id',$classifyId)
            ->select(DB::raw('sum(impressions) as impressions,
                sum(clicks) as clicks,
                sum(total_units) as total_units,
                sum(spend) as spend,
                sum(total_sales) as total_sales'))
            ->first();
        $re = [];
        $re['impressions'] = ($ads->impressions ?? 0) + ($adsVc->impressions ?? 0) + ($adsBc->impressions ?? 0) + ($adsDSP->impressions ?? 0);
        $re['clicks'] = ($ads->clicks ?? 0) + ($adsVc->clicks ?? 0) + ($adsBc->clicks ?? 0) + ($adsDSP->clicks ?? 0);
        $re['total_units'] = ($ads->total_units ?? 0) + ($adsVc->total_units ?? 0) + ($adsBc->total_units ?? 0) + ($adsDSP->total_units ?? 0);
        $re['spend'] = ($ads->spend ?? 0) + ($adsVc->spend ?? 0) + ($adsBc->spend ?? 0) + ($adsDSP->spend ?? 0);
        $re['total_sales'] = ($ads->total_sales ?? 0) + ($adsVc->total_sales ?? 0) + ($adsBc->total_sales ?? 0) + ($adsDSP->total_sales ?? 0);
        return $re;
    }
}
